package jdbc;

import bean.User;
import utils.JDBCUtils;
import org.junit.Test;

import java.sql.*;

/**
 * Date:2022/2/26
 * Author:ybc
 * Description:
 * JDBC的步骤：
 * 1、注册驱动，即将MySQL的驱动类加载到内存中
 * 2、创建连接，通过连接地址、用户名、密码创建连接
 * 连接地址使用的是统一资源定位符，根据具体的协议访问指定IP地址所对应主机上某个端口号所对应的应用程序
 * 统一资源定位符的格式-->协议://ip:port/资源路径
 * 连接MySQL的连接地址-->jdbc:mysql://localhost:3306/数据库?characterEncoding=utf8
 * 3、创建预编译对象
 * 4、执行SQL语句获取结果
 * executeUpdate()执行增删改，返回int类型的结果，表示受影响的行数
 * executeQuery()执行查询，返回ResultSet结果集
 * ResultSet中有方法：
 * next()判断结果集中是否有下一条数据，并获取下一条数据，因此获取结果集数据时，一次循环只能执行一次next()
 * getXxx(int columnCount)：根据列数获取某一列的数据
 * getXxx(String columnName)：根据列名获取某一列的数据
 */
public class CRUDTest {

    @Test
    public void testMySQLDataToJavaBean() {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root", "123456");
            statement = connection.createStatement();
            String sql = "select * from t_user";
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");
                String email = rs.getString("email");
                User user = new User(id, username, password, age, sex, email);
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, statement, connection);
        }
    }

    @Test
    public void testSelect(){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root", "123456");
            statement = connection.createStatement();
            String sql = "select * from t_user";
            rs = statement.executeQuery(sql);
            while (rs.next()){
                //表示结果集中有下一条数据
                /*int id = rs.getInt(1);
                String username = rs.getString(2);
                String password = rs.getString(3);
                int age = rs.getInt(4);
                String sex = rs.getString(5);
                String email = rs.getString(6);*/
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");
                String email = rs.getString("email");
                System.out.println(id + "\t" + username + "\t" + password + "\t" + age + "\t" + sex + "\t" + email);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, statement, connection);
        }
    }

    @Test
    public void testUpdate(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root", "123456");
            statement = connection.createStatement();
            String sql = "update t_user set password = '123456', age = 30, sex = '男', email = 'zhangsan@qq.com' where id = 2";
            int result = statement.executeUpdate(sql);
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, statement, connection);
        }
    }

    @Test
    public void testDelete(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root", "123456");
            statement = connection.createStatement();
            String sql = "delete from t_user where id = 6";
            int result = statement.executeUpdate(sql);
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, statement, connection);
        }
    }

    @Test
    public void testInsert(){
        Connection connection = null;
        Statement statement = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8";
            String username = "root";
            String password = "123456";
            connection = DriverManager.getConnection(url, username, password);
            //创建预编译对象
            statement = connection.createStatement();
            String sql = "insert into t_user values(null,'admin','123456',23,'男','123456@atguigu.com')";
            //执行SQL
            int result = statement.executeUpdate(sql);
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, statement, connection);
            /*try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
        }
    }

}
